package com.qp.basics.estivate.utils;

import java.io.*;
import java.util.*;

public class Formatter {

	private static final Set BEGIN_CLAUSES = new HashSet();
	private static final Set END_CLAUSES = new HashSet();
	private static final Set LOGICAL = new HashSet();
	private static final Set QUANTIFIERS = new HashSet();
	private static final Set DML = new HashSet();
	private static final Set MISC = new HashSet();
	static {

		BEGIN_CLAUSES.add("left");
		BEGIN_CLAUSES.add("right");
		BEGIN_CLAUSES.add("inner");
		BEGIN_CLAUSES.add("outer");
		BEGIN_CLAUSES.add("group");
		BEGIN_CLAUSES.add("order");

		END_CLAUSES.add("where");
		END_CLAUSES.add("set");
		END_CLAUSES.add("having");
		END_CLAUSES.add("join");
		END_CLAUSES.add("from");
		END_CLAUSES.add("by");
		END_CLAUSES.add("join");
		END_CLAUSES.add("into");
		END_CLAUSES.add("union");

		LOGICAL.add("and");
		LOGICAL.add("or");
		LOGICAL.add("when");
		LOGICAL.add("else");
		LOGICAL.add("end");

		QUANTIFIERS.add("in");
		QUANTIFIERS.add("all");
		QUANTIFIERS.add("exists");
		QUANTIFIERS.add("some");
		QUANTIFIERS.add("any");

		DML.add("insert");
		DML.add("update");
		DML.add("delete");

		MISC.add("select");
		MISC.add("on");
		// MISC.add("values");

	}

	String indentString = "    ";
	String initial = "\n    ";

	boolean beginLine = true;
	boolean afterBeginBeforeEnd = false;
	boolean afterByOrSetOrFromOrSelect = false;
	boolean afterValues = false;
	boolean afterOn = false;
	boolean afterBetween = false;
	boolean afterInsert = false;
	boolean isCommentLine = false;

	int commentLineNum = 0;

	int inFunction = 0;
	int parensSinceSelect = 0;
	private LinkedList parenCounts = new LinkedList();
	private LinkedList afterByOrFromOrSelects = new LinkedList();

	int indent = 0;

	StringBuffer result = new StringBuffer();
	StringTokenizer tokens;
	String lastToken;
	String token;
	String lcToken;

	public Formatter(String sql) {
		tokens = new StringTokenizer(sql, "()+*/-=<>'`\"[],"
				+ StringHelper.WHITESPACE, true);
	}

	public Formatter setInitialString(String initial) {
		this.initial = initial;
		return this;
	}

	public Formatter setIndentString(String indent) {
		this.indentString = indent;
		return this;
	}

	public List<String> format() {

		result.append(initial);

		while (tokens.hasMoreTokens()) {
			token = tokens.nextToken();
			lcToken = token.toLowerCase();

			if ("-".equals(token)) {
				commentLineNum++;
				if (commentLineNum >= 2) {
					String t;
					do {
						t = tokens.nextToken();
						token += t;
					} while (!"\n".equals(t) && tokens.hasMoreTokens());
					commentLineNum = 0;
				}
			} else if ("'".equals(token)) {
				String t;
				do {
					t = tokens.nextToken();
					token += t;
				} while (!"'".equals(t) && tokens.hasMoreTokens()); // cannot
																	// handle
																	// single
																	// quotes
			} else if ("\"".equals(token)) {
				String t;
				do {
					t = tokens.nextToken();
					token += t;
				} while (!"\"".equals(t));
			}

			if (!"-".equals(token))
				commentLineNum = 0; // 不是注释行

			if (afterByOrSetOrFromOrSelect && ",".equals(token)) {
				commaAfterByOrFromOrSelect();
			} else if (afterOn && ",".equals(token)) {
				commaAfterOn();
			}

			else if ("(".equals(token)) {
				openParen();
			} else if (")".equals(token)) {
				closeParen();
			}

			else if (BEGIN_CLAUSES.contains(lcToken)) {
				beginNewClause();
			}

			else if (END_CLAUSES.contains(lcToken)) {
				endNewClause();
			}

			else if ("select".equals(lcToken)) {
				select();
			}

			else if (DML.contains(lcToken)) {
				updateOrInsertOrDelete();
			}

			else if ("values".equals(lcToken)) {
				values();
			}

			else if ("on".equals(lcToken)) {
				on();
			}

			else if (afterBetween && lcToken.equals("and")) {
				misc();
				afterBetween = false;
			}

			else if (LOGICAL.contains(lcToken)) {
				logical();
			}

			else if (isWhitespace(token)) {
				white();
			} else {
				misc();
			}

			if (";".equals(token))
				indent = 0;
			if (!isWhitespace(token))
				lastToken = lcToken;

		}
		return this.spliteSql(result.toString());
	}

	private void commaAfterOn() {
		out();
		indent--;
		newline();
		afterOn = false;
		afterByOrSetOrFromOrSelect = true;
	}

	private void commaAfterByOrFromOrSelect() {
		out();
		newline();
	}

	private void logical() {
		if ("end".equals(lcToken))
			indent--;
		newline();
		out();
		beginLine = false;
	}

	private void on() {
		indent++;
		afterOn = true;
		newline();
		out();
		beginLine = false;
	}

	private void misc() {
		out();
		if ("between".equals(lcToken)) {
			afterBetween = true;
		}
		if (afterInsert) {
			newline();
			afterInsert = false;
		} else {
			beginLine = false;
			if ("case".equals(lcToken)) {
				indent++;
			}
		}
	}

	private void white() {
		if (!beginLine) {
			result.append(" ");
		}
	}

	private void updateOrInsertOrDelete() {
		out();
		indent++;
		beginLine = false;
		if ("update".equals(lcToken))
			newline();
		if ("insert".equals(lcToken))
			afterInsert = true;
	}

	private void select() {
		out();
		indent++;
		newline();
		parenCounts.addLast(new Integer(parensSinceSelect));
		afterByOrFromOrSelects.addLast(new Boolean(afterByOrSetOrFromOrSelect));
		parensSinceSelect = 0;
		afterByOrSetOrFromOrSelect = true;
	}

	private void out() {
		result.append(token);
	}

	private void endNewClause() {
		if (!afterBeginBeforeEnd) {
			indent--;
			if (afterOn) {
				indent--;
				afterOn = false;
			}
			newline();
		}
		out();
		if (!"union".equals(lcToken))
			indent++;
		newline();
		afterBeginBeforeEnd = false;
		afterByOrSetOrFromOrSelect = "by".equals(lcToken)
				|| "set".equals(lcToken) || "from".equals(lcToken);
	}

	private void beginNewClause() {
		if (!afterBeginBeforeEnd) {
			if (afterOn) {
				indent--;
				afterOn = false;
			}
			indent--;
			newline();
		}
		out();
		beginLine = false;
		afterBeginBeforeEnd = true;
	}

	private void values() {
		indent--;
		newline();
		out();
		indent++;
		newline();
		afterValues = true;
	}

	private void closeParen() {
		parensSinceSelect--;
		if (parensSinceSelect < 0) {
			indent--;
			parensSinceSelect = ((Integer) parenCounts.removeLast()).intValue();
			afterByOrSetOrFromOrSelect = ((Boolean) afterByOrFromOrSelects
					.removeLast()).booleanValue();
		}
		if (inFunction > 0) {
			inFunction--;
			out();
		} else {
			if (!afterByOrSetOrFromOrSelect) {
				indent--;
				newline();
			}
			out();
		}
		beginLine = false;
	}

	private void openParen() {
		if (isFunctionName(lastToken) || inFunction > 0) {
			inFunction++;
		}
		beginLine = false;
		if (inFunction > 0) {
			out();
		} else {
			out();
			if (!afterByOrSetOrFromOrSelect) {
				indent++;
				newline();
				beginLine = true;
			}
		}
		parensSinceSelect++;
	}

	private static boolean isFunctionName(String tok) {
		final char begin = tok.charAt(0);
		final boolean isIdentifier = Character.isJavaIdentifierStart(begin)
				|| '"' == begin;
		return isIdentifier && !LOGICAL.contains(tok)
				&& !END_CLAUSES.contains(tok) && !QUANTIFIERS.contains(tok)
				&& !DML.contains(tok) && !MISC.contains(tok);
	}

	private static boolean isWhitespace(String token) {
		return StringHelper.WHITESPACE.indexOf(token) >= 0;
	}

	private void newline() {
		result.append("\n");
		for (int i = 0; i < indent; i++) {
			result.append(indentString);
		}
		beginLine = true;
	}

	public List<String> spliteSql(String sqlString) {
		List<String> sqlStringList = new ArrayList<String>();
		char[] sqlCharArr = sqlString.toCharArray();
		Stack stack = new Stack();
		String sql = "";
		int commLineNum = 0;
		for (int i = 0; i < sqlCharArr.length; i++) {
			char sqlChar = sqlCharArr[i];
			sql += sqlChar;
			if ('-' == sqlChar)
				commLineNum++;
			else
				commLineNum = 0;
			if (commLineNum >= 2) {
				while (i < sqlCharArr.length && '\n' != sqlCharArr[i]) {
					sql += sqlCharArr[i];
					i++;
				}
				sql += '\n';
				commLineNum = 0;
				continue;
			}

			if (sqlChar == '\'' && stack.size() == 0) {
				stack.push('\'');
				continue;
			} else if (sqlChar == '\'' && stack.size() != 0) {
				stack.pop();
				continue;
			}

			if (sqlChar == ';' && stack.size() != 0) {

				continue;
			} else if (sqlChar == ';' && stack.size() == 0) {
				if (!"".equals(sql.replace(";", "").trim()))
					sqlStringList.add(sql);
				sql = "";
			}
		}

		return sqlStringList;
	}

	public static String readFile2Str(String fileName) throws IOException {
		String charset = System.getProperty("file.encoding");
		return readFile2Str(fileName, charset);
	}

	public static String readFile2Str(String fileName, String charset)
			throws IOException {
		File file = new File(fileName);
		InputStreamReader isr = new InputStreamReader(
				new FileInputStream(file), charset);
		BufferedReader bf = new BufferedReader(isr);
		String content = "";
		StringBuilder sb = new StringBuilder();
		while (content != null) {
			content = bf.readLine();
			if (content == null) {
				break;
			}
			if (content.trim().startsWith("--")) {
				content += "\n";
				continue;
			}
			sb.append(content);
		}
		bf.close();
		return sb.toString();
	}

	public static void main(String[] args) throws IOException {
		String sqlStr = Formatter.readFile2Str(
				"D:/eclipse/workspace/yq_estivate/document/check.sql", "utf8");
		// sqlStr =
		// "select * from address a where  a.name =  ''';hkj'';nkj''';";
		List<String> strList = null;
		// if ( args.length>0 ){
		strList = new Formatter(sqlStr).format();
		// }
		for (String Str : strList) {
			System.out.println(Str);
			System.out
					.println("------------------------------------------------------------");
		}

	}

}
